package TimesheetManagemen;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.Connection;






import com.mysql.jdbc.Statement;

public class TimesheetDAO {
	
	private Connection conn=null;

	public TimesheetDAO() {
		String userName = "root";
		String password = "123456";
		String url = "jdbc:mysql://localhost/timesheet?"
				+ "user=root&password=123456";

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			conn = java.sql.DriverManager
					.getConnection(url, userName, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}}
	
public int addTimesheet(Timesheet ts){
	String query="insert into timesheet values(?,?,?,?,?,?,?,?,?)";
	int kq=0;
	try {
		PreparedStatement ps = conn.prepareStatement(query);
		ps.setInt(1, ts.getId());
		ps.setString(2, ts.getDate());
		ps.setInt(3, ts.getDuration());
		ps.setString(4, ts.getDescription());
		ps.setString(5, ts.getActivity());
		ps.setString(6, ts.getOvertime());
		ps.setString(7, ts.getLocation());
		ps.setInt(8, ts.getEmployeeid());
		ps.setInt(9, ts.getProjectid());
		kq= ps.executeUpdate();
		return kq;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return kq;
	}
public void addTs(Timesheet ts1) throws ClassNotFoundException, SQLException{
	TimesheetDAO ts = new TimesheetDAO();
	ProjectDAO pjo=new ProjectDAO();
	EmployeeDAO dao = new EmployeeDAO();
	 ArrayList<Timesheet> list = ts.getTimesheet();
	 ArrayList<Employee> listemp=dao.getEmployee();
	 ArrayList<Project> listpj=pjo.getProject();
	    int a=0;
		int b=0;
		int e=0;
		for(int i=0;i<list.size();i++){
			for(int j=0;j<listemp.size();j++){
				for(int m=0;m<listpj.size();m++)
		{
			int c=list.get(i).getId();
			if(c==ts1.getId())
			{a=1;
			break;}
			int g=listemp.get(j).getId();
			if(g==ts1.getEmployeeid()){
				b=1;break;
			}
			int h=listpj.get(m).getId();
			if(h==ts1.getProjectid()){
				e=1;break;
			}
		}}}
		 if( a==1){
				System.out.println("Id exist ");	
		    }
		 else if(b==0){
			 System.out.println("Not Found Employeeid ");
		 }
		 else if(e==0){
			 System.out.println("Not Found Projectid");
		 }
		     else if(ts.addTimesheet(ts1)==1) {
		     	System.out.println("Add Successfull");   	
		}
		     else if(ts.addTimesheet(ts1)==0 ){
					System.out.println("Not Add Successfull");
			     }		  
}
public int updateTimesheet(Timesheet ts) {
	String query = "update timesheet set date=?, duration=?, description=?,activity=?,overtime=?,location=?,employeeid=?,projectid=? where id=?";
	int kq = 0;
	try {
		PreparedStatement ps = conn.prepareStatement(query);
		ps.setString(1, ts.getDate());
		ps.setInt(2, ts.getDuration());
		ps.setString(3, ts.getDescription());
		ps.setString(4, ts.getActivity());
		ps.setString(5, ts.getOvertime());
		ps.setString(6, ts.getLocation());
		ps.setInt(7, ts.getEmployeeid());
		ps.setInt(8, ts.getProjectid());
		ps.setInt(9, ts.getId());
		kq = ps.executeUpdate();
		return kq;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return kq;
}
public void updateTs(Timesheet ts1) throws ClassNotFoundException, SQLException{
	TimesheetDAO ts = new TimesheetDAO();
	ProjectDAO pjo=new ProjectDAO();
	EmployeeDAO dao = new EmployeeDAO();
	 ArrayList<Timesheet> list = ts.getTimesheet();
	 ArrayList<Employee> listemp=dao.getEmployee();
	 ArrayList<Project> listpj=pjo.getProject();
	 int a=0;
		int b=0;
		int d=0;
		for(int i=0;i<list.size();i++){
			for(int j=0;j<listemp.size();j++){
				for(int h=0;h<listpj.size();h++){
					int c=list.get(i).getId();
					if(c==ts1.getId()){
						a=1;break;	}
					int e=listemp.get(j).getId();
					if(e==ts1.getEmployeeid()){
						b=1;break;}
					int f=listpj.get(h).getId();
					if(f==ts1.getProjectid()){
						d=1;break;
					}}}	}
		if(a==0){
			System.out.println("Not Found Id");
		}
		else if(b==0){
			System.out.println("Not Found Employeeid");
		}
		else if(d==0){
			System.out.println("Not Found Projectid");
		}
		else if(ts.updateTimesheet(ts1)==1){
			System.out.println("Update Successfull");
		}
}

public int deleteTimesheet(int id) {
	String query = "delete from timesheet where id=?";
	int kq = 0;
	try {
		PreparedStatement ps = conn.prepareStatement(query);
		ps.setInt(1, id);
		kq = ps.executeUpdate();
		return kq;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return kq;
}
public ArrayList<Timesheet> getTimesheet() throws ClassNotFoundException, SQLException {
	ArrayList<Timesheet> dsts = new ArrayList<>();

	String query = "SELECT * FROM timesheet";
	

	try {
		PreparedStatement ps = conn.prepareStatement(query);
		ResultSet rs = ps.executeQuery();
		
		while (rs.next()) {
			int id = rs.getInt(1);
			String date = rs.getString(2);
			int duration = rs.getInt(3);
			String description=rs.getString(4);
			String activity=rs.getString(5);
			String overtime=rs.getString(6);
			String location=rs.getString(7);
			int employeeid=rs.getInt(8);
			int projectid=rs.getInt(9);
			
			Timesheet ts = new Timesheet(id, date, duration,description,activity,overtime,location,employeeid,projectid);
			dsts.add(ts);

		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}

	return dsts;
}



}
